﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ProjectYANApi.Models;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using YanOnline.Function;

namespace ProjectYANApi.Function
{
    public class clsFunction
    {

        public static void SaveLogClearCache(string strUserName, string strAction, string strContentId, string strLogDate, string strIPUser)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            DataSet retVal = new DataSet();
            SqlConnection dbConn = new SqlConnection(strConn);
            SqlCommand dbCmd = new SqlCommand("tblLogSystem_Insert", dbConn);
            dbCmd.Parameters.AddWithValue("@UserName", strUserName);
            dbCmd.Parameters.AddWithValue("@Action", strAction);
            dbCmd.Parameters.AddWithValue("@ContentId", 0);
            dbCmd.Parameters.AddWithValue("@LogDate", strLogDate);
            dbCmd.Parameters.AddWithValue("@IPUser", strIPUser);
            dbCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static List<ListContent> Api_tblContentGetHomePage(int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetHomePage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetBlogZoneWithPage(int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetBlogZoneWithPage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetByZoneWithPage(int PageSize, int pageNumber, int zoneId)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetByZoneWithPage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    cmd.Parameters.AddWithValue("@zoneId", zoneId);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetCelebZoneWithPage(int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetCelebZoneWithPage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetVideoZoneWithPage(int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetVideoZoneWithPage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetEnterZoneWithPage(int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetEnterZoneWithPage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }


        public static List<ListContent> Api_tblContentGetFeaturedWithPage(int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetFeaturedWithPage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetNewNewsWithPage(int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetNewNewsWithPage";
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@pageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetFeatured()
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetFeatured";
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentGetFeaturedByZone(int ZoneId, int Num)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetFeaturedByZone";
                    cmd.Parameters.AddWithValue("@ZoneId", ZoneId);
                    cmd.Parameters.AddWithValue("@Num", Num);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        //
        public static List<ListContent> Api_tblContentGetMostRead(int ZoneId, int Num)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetMostRead";
                    cmd.Parameters.AddWithValue("@ZoneId", ZoneId);
                    cmd.Parameters.AddWithValue("@Num", Num);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.strLink = "http://yan.vn/" + StringUtility.UnicodeToKoDau(tbl.Title) + "-v-" + tbl.Id + ".html";
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static List<ListContent> Api_tblContentEnterGetByZoneAndCategory(int ZoneId, int CatId, int PageSize, int PageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentEnterGetByZoneAndCategory";
                    cmd.Parameters.AddWithValue("@ZoneId", ZoneId);
                    cmd.Parameters.AddWithValue("@IdCat", CatId);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@PageNumber", PageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        //
        public static List<ListContent> Api_tblContentGetByZoneAndCategory(int ZoneId, int CatId, int PageSize, int PageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentGetByZoneAndCategory";
                    cmd.Parameters.AddWithValue("@ZoneId", ZoneId);
                    cmd.Parameters.AddWithValue("@IdCat", CatId);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@PageNumber", PageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        //
        public static List<ListContent> Api_tblContentSearchKeyword(string pKeyword, int PageSize, int pageNumber)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<ListContent> lst = new List<ListContent>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"Api_tblContentSearchKeyword";
                    cmd.Parameters.AddWithValue("@pKeyword", pKeyword);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        ListContent tbl = new ListContent();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        tbl.DistributionDate = ConvertUtility.ToDateTime(rs["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                        tbl.Avatar = rs["Avatar"].ToString();
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.ZoneName = rs["ZoneName"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        public static ContentInfo GetInfo(int id)
        {
            string lOld = "src=\"//www.youtube.com";
            string lNew = "src=\"http://www.youtube.com";

            ContentInfo retVal = new ContentInfo();
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            SqlConnection dbConn = new SqlConnection(strConn);
            SqlCommand dbCmd = new SqlCommand("Api_tblContent_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", id);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal.Id = int.Parse(dr["Id"].ToString());
                    retVal.Avatar = dr["Avatar"].ToString();
                    retVal.Body = dr["Boby"].ToString().Replace(lOld, lNew).Replace("src=\"/embed", "title=\"/embed");
                    retVal.DistributionDate = ConvertUtility.ToDateTime(dr["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                    retVal.Source = dr["Source"].ToString();
                    retVal.TagDisplay = dr["TagDisplay"].ToString();
                    retVal.Teaser = dr["Teaser"].ToString();
                    retVal.Title = dr["Title"].ToString();
                    retVal.ViewCount = int.Parse(dr["ViewCount"].ToString());
                    retVal.strLink = "http://yan.vn/" + StringUtility.UnicodeToKoDau(retVal.Title) + "-" + retVal.Id + ".html";
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public static ContentInfo GetInfoVideo(int id)
        {
            string lOld = "src=\"//www.youtube.com";
            string lNew = "src=\"http://www.youtube.com";

            ContentInfo retVal = new ContentInfo();
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            SqlConnection dbConn = new SqlConnection(strConn);
            SqlCommand dbCmd = new SqlCommand("Api_tblContentAPIGetInfoVideo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", id);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal.Id = int.Parse(dr["Id"].ToString());
                    retVal.Avatar = dr["Avatar"].ToString();
                    retVal.Body = dr["Boby"].ToString().Replace(lOld, lNew).Replace("src=\"/embed", "title=\"/embed");
                    retVal.DistributionDate = ConvertUtility.ToDateTime(dr["DistributionDate"]).ToString("dd/MM/yyyy HH:mm");
                    retVal.Source = dr["Source"].ToString();
                    retVal.TagDisplay = dr["TagDisplay"].ToString();
                    retVal.Teaser = dr["Teaser"].ToString();
                    retVal.Title = dr["Title"].ToString();
                    retVal.ViewCount = int.Parse(dr["ViewCount"].ToString());
                    retVal.Folder = int.Parse(dr["Folder"].ToString());
                    retVal.VideoName = dr["VideoName"].ToString();
                    retVal.HttpAddress = dr["HttpAddress"].ToString();

                    if (retVal.Folder >= 201403)
                    {
                        string strName = retVal.VideoName.ToLower().Replace(".mp4", "");
                        retVal.urlVideo = retVal.HttpAddress + "YanVideo/" + retVal.Folder + "/" + strName + "/" + strName + ".m3u8";
                    }
                    else
                    {
                        retVal.urlVideo = retVal.HttpAddress + "YanVideo/" + retVal.Folder + "/" + retVal.VideoName;
                    }

                    retVal.strLink = "http://yan.vn/" + StringUtility.UnicodeToKoDau(retVal.Title) + "-v-" + retVal.Id + ".html";
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }


        public static List<tblNews> tblContentNewsSearchTagGetAll(string strTag)
        {
            string strConn = ConfigurationManager.ConnectionStrings["YanOnlineConnectionString"].ConnectionString;
            List<tblNews> lst = new List<tblNews>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"tblContentNewsSearchTagGetAll";
                    cmd.Parameters.AddWithValue("@pKeyword", strTag);

                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        tblNews tbl = new tblNews();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.Title = rs["Title"].ToString();
                        tbl.Teaser = rs["Teaser"].ToString();
                        tbl.ZoneId = int.Parse(rs["ZoneId"].ToString());
                        tbl.CategoryId = int.Parse(rs["CategoryId"].ToString());
                        tbl.CategoryName = rs["CategoryName"].ToString();
                        tbl.Avatar = rs["Avatar"].ToString().Replace("***", "");
                        tbl.Avatar2x1 = rs["Avatar"].ToString().Replace("***", "352x176_");
                        tbl.Avatar1x1 = rs["Avatar"].ToString().Replace("***", "300x300_");
                        tbl.DistributionDate = DateTime.Parse(rs["DistributionDate"].ToString());
                        tbl.TagDisplay = rs["TagDisplay"].ToString();
                        tbl.UrlLink = myCommon.GeneraLink(tbl.Title, tbl.ZoneId, tbl.Id);
                        tbl.BlogId = rs["BlogId"].ToString();
                        tbl.IdType = int.Parse(rs["IdType"].ToString());
                        tbl.ViewCount = int.Parse(rs["ViewCount"].ToString());
                        lst.Add(tbl);
                    }
                    rs.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }
    }
}